﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data;

namespace Dry.DbInterpreter
{
    public class DbTableColumnInfo : General.DryBase
    {
        public static SqlDataReader GetDbTableColumnInfo() 
        {
            Database db = GetDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(GetSqlTableColumnInfo());
            
            return (SqlDataReader) db.ExecuteReader(dbCommand);
        }

        private static string GetSqlTableColumnInfo()
        {
            return
                    @"SELECT DISTINCT 
	                t.name + '_' + c.name AS TableColumnEnum	
                FROM       
	                sys.objects o inner join 
	                sys.columns c on o.object_id=c.object_id inner join
	                sys.tables t on o.object_id=t.object_id inner join 
	                sys.types ty on c.user_type_id=ty.user_type_id LEFT OUTER JOIN  
	                sys.extended_properties ep on ep.major_id = c.object_id AND ep.minor_id = c.column_id LEFT OUTER JOIN 
	                sys.foreign_key_columns fk on fk.parent_object_id=o.object_id  and fk.parent_column_id=c.column_id LEFT OUTER JOIN
	                sys.index_columns ic on ic.object_id=o.object_id and ic.column_id=c.column_id LEFT OUTER JOIN
	                sys.indexes i on ic.object_id=i.object_id and ic.index_column_id=c.column_id and i.is_primary_key=1
                WHERE     
	                t.name like '%tbl%'
	                and (i.is_primary_key = 1 or fk.parent_column_id is not null)";
        }
    }
}
